﻿#region

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Windows.Forms;
using LCL;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using OfficeOpenXml;

#endregion

namespace System
{
    /// <summary>
    /// NPOI EPPlus
    /// </summary>
    public static class ExcelOperate
    {
        /// <summary>
        ///     导出excel(Excel 97-2003)
        ///     默认第一行为标头
        /// </summary>
        /// <param name="dt">导出Excel数据源</param>
        /// <param name="filename">excel文档路径</param>
        /// <param name="sheetName">Name of the sheet.</param>
        public static bool GetExcel(DataTable dt, string filename, string sheetName = "Sheet1") //导出Excel
        {
            try {
                var book = new HSSFWorkbook();
                ISheet sheet = book.CreateSheet(sheetName);
                IRow row = sheet.CreateRow(0);
                //创建表头
                for (int i = 0; i < dt.Columns.Count; i++) {
                    row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                }
                //创建内容
                for (int i = 0; i < dt.Rows.Count; i++) {
                    IRow row2 = sheet.CreateRow(i + 1);
                    for (int j = 0; j < dt.Columns.Count; j++) {
                        row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                    }
                }
                //写入到客户端
                var file = new FileStream(filename, FileMode.Create);
                book.Write(file);
                file.Close();
                return true;
            } catch (Exception ex) {
                Logger.Error("导出Excel GetExcel" + Path.GetFileName(filename), ex);
                return false;
            }
        }

        /// <summary>
        ///     读取excel(97-2003)
        ///     默认第一行为标头
        /// </summary>
        /// <param name="strFileName">excel文档路径</param>
        /// <returns></returns>
        public static DataTable Import(string strFileName) {
            var dt = new DataTable();

            HSSFWorkbook hssfworkbook;
            using (var file = new FileStream(strFileName, FileMode.Open, FileAccess.Read)) {
                hssfworkbook = new HSSFWorkbook(file);
                file.Close();
            }


            var sheet = (HSSFSheet)hssfworkbook.GetSheetAt(0);

            hssfworkbook.GetSheetAt(0);
            IEnumerator rows = sheet.GetRowEnumerator();

            var headerRow = (HSSFRow)sheet.GetRow(0);

            if (headerRow == null) {
                return dt;
            }

            int cellCount = headerRow.LastCellNum;

            for (int j = 0; j < cellCount; j++) {
                var cell = (HSSFCell)headerRow.GetCell(j);
                if (!dt.Columns.Contains(cell.ToString())) {
                    dt.Columns.Add(cell.ToString());
                } else {
                    dt.Columns.Clear();
                    return dt;
                }
            }

            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) {
                var row = (HSSFRow)sheet.GetRow(i);
                if (row != null) {
                    DataRow dataRow = dt.NewRow();
                    for (int j = row.FirstCellNum; j <= cellCount; j++) {
                        if (row.GetCell(j) != null) {
                            dataRow[j] = row.GetCell(j).ToString().Trim();
                        }
                    }
                    dt.Rows.Add(dataRow);
                }

            }
            return dt;
        }

        /// <summary>
        ///     导出excel(2007 Excel)
        ///     默认第一行为标头
        /// </summary>
        /// <param name="table">导出Excel数据源</param>
        /// <param name="filename">excel文档路径</param>
        /// <param name="sheetName">Name of the sheet.</param>
        public static bool CreateByExcelLibrary(DataTable table, string filename, string sheetName) {
            try {
                using (var package = new ExcelPackage()) {
                    ExcelWorksheet sheet = package.Workbook.Worksheets.Add(sheetName);
                    int colCount = table.Columns.Count;
                    for (int i = 0; i < colCount; i++) {
                        sheet.Cells[1, i + 1].Value = table.Columns[i].Caption;
                    }
                    int k = 2;
                    foreach (DataRow row in table.Rows) {
                        for (int i = 0; i < colCount; i++) {
                            sheet.Cells[k, i + 1].Value = row[i];
                        }
                        k++;
                    }
                    var file = new FileStream(filename, FileMode.Create);
                    package.SaveAs(file);
                    file.Close();
                    return true;
                }
            } catch (Exception ex) {
                Logger.Error("导出Excel CreateByExcelLibrary", ex);
                return false;
            }
        }


        /// <summary>
        ///     读取excel(2007)
        ///     默认第一行为标头
        /// </summary>
        /// <param name="filename">excel文档路径</param>
        /// <returns></returns>
        public static DataTable ReadByExcelLibrary(string filename) {
            var table = new DataTable();
            var file = new FileStream(filename, FileMode.Open, FileAccess.Read);
            try {
                using (var package = new ExcelPackage(file)) {
                    ExcelWorksheet sheet = package.Workbook.Worksheets[1];
                    int colCount = sheet.Dimension.End.Column;
                    int rowCount = sheet.Dimension.End.Row;

                    for (ushort j = 1; j <= colCount; j++) {
                        if (null != sheet.Cells[1, j].Value)
                            table.Columns.Add(new DataColumn(sheet.Cells[1, j].Value.ToString()));
                    }

                    for (ushort i = 2; i <= rowCount; i++) {
                        DataRow row = null;
                        for (ushort j = 1; j <= colCount; j++) {
                            if (null != sheet.Cells[i, j].Value && (j) <= table.Columns.Count) {
                                if (row == null)
                                    row = table.NewRow();
                                row[j - 1] = sheet.Cells[i, j].Value;
                            }
                        }
                        if (null != row)
                            table.Rows.Add(row);
                    }
                }
            } finally {
                file.Close();
            }
            return table;
        }

        /// <summary>
        ///     填充DataTable
        ///     默认第一行为标头
        /// </summary>
        /// <param name="export">数据表</param>
        /// <param name="lexport">数据源</param>
        /// <returns></returns>
        public static DataTable FillTable(DataTable export, List<String> lexport) {
            //DataTable export = new DataTable();
            // List<String> lexport = new List<String>();
            int i = 0;
            if (lexport.Count != 0) {
                export.Columns.Add("行号", typeof(string));
                export.Columns.Add("错误原因", typeof(string));
                while (i < lexport.Count) {
                    DataRow newRow = export.NewRow();
                    newRow["行号"] = lexport[i];
                    newRow["错误原因"] = lexport[++i];
                    export.Rows.Add(newRow);
                    i++;
                }
            }
            return export;
        }

        /// <summary>
        ///     填充excel
        ///     默认第一行为标头
        /// </summary>
        /// <param name="dt">The dt.</param>
        /// <param name="FileName">文件路径</param>
        public static void DataSetToExcel(DataTable dt, string FileName) {
            string ext = Path.GetExtension(FileName);

            if (ext.Equals(".xls")) {
                #region  97-2003格式

                var file = new FileStream(FileName, FileMode.Open);
                var bookExit = new HSSFWorkbook(file);
                try {
                    if (dt == null)
                        return;

                    ISheet gsheet = bookExit.GetSheet("错误信息");

                    for (int i = gsheet.FirstRowNum; i <= gsheet.LastRowNum; i++) {
                        IRow row1 = gsheet.GetRow(i);

                        gsheet.RemoveRow(row1);
                    }
                    IRow row = gsheet.CreateRow(0);
                    //创建表头
                    for (int i = 0; i < dt.Columns.Count; i++) {
                        row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                    }

                    //创建内容
                    for (int i = 0; i < dt.Rows.Count; i++) {
                        IRow row2 = gsheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++) {
                            row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                } catch (Exception) {
                    ISheet csheet = bookExit.CreateSheet("错误信息");
                    IRow row = csheet.CreateRow(0);
                    //创建表头
                    for (int i = 0; i < dt.Columns.Count; i++) {
                        row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
                    }

                    //创建内容
                    for (int i = 0; i < dt.Rows.Count; i++) {
                        IRow row2 = csheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++) {
                            row2.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
                        }
                    }
                } finally {
                    var anyfile = new FileStream(FileName, FileMode.Create);
                    bookExit.Write(anyfile);
                    file.Close();
                    file.Dispose();
                    anyfile.Close();
                    anyfile.Dispose();
                }

                #endregion
            } else {
                #region 2007格式

                if (ext.Equals(".xlsx")) {
                    using (var package = new ExcelPackage()) {
                        try {
                            int count = package.Workbook.Worksheets.Count;

                            ExcelWorksheet sheet = package.Workbook.Worksheets.Add("错误信息");


                            int colCount = dt.Columns.Count;

                            for (int i = 0; i < colCount; i++) {
                                sheet.Cells[1, i + 1].Value = dt.Columns[i].Caption;
                            }

                            int k = 2;
                            foreach (DataRow row in dt.Rows) {
                                for (int i = 0; i < colCount; i++) {
                                    sheet.Cells[k, i + 1].Value = row[i];
                                }
                                k++;
                            }
                        } finally {
                            var file = new FileStream(FileName, FileMode.Create);
                            package.SaveAs(file);
                            file.Close();
                        }
                    }
                }

                #endregion
            }
        }

        /// <summary>
        ///     Writes the error message.
        /// </summary>
        /// <param name="importFileName">Name of the import file.</param>
        /// <param name="list">The list.</param>
        /// CreatedBy:Kaichun.Qiao
        /// CreatedDate:2012/12/2 22:33
        public static void WriteErrorMessage(string importFileName, IList<ImportErrorCellInfo> list) {
            string ext = Path.GetExtension(importFileName);
            if (ext.Equals(".xls")) {
                #region  97-2003格式
                var file = new FileStream(importFileName, FileMode.Open);
                var bookExit = new HSSFWorkbook(file);
                try {
                    if (list.Count == 0) return;
                    ISheet errorSheet = bookExit.GetSheet("错误信息") ?? bookExit.CreateSheet("错误信息");
                    for (int i = errorSheet.FirstRowNum; i <= errorSheet.LastRowNum; i++) {
                        IRow row1 = errorSheet.GetRow(i);
                        if (null != row1) {
                            errorSheet.RemoveRow(row1);
                        }
                    }
                    IRow row = errorSheet.CreateRow(0);
                    //创建错误页表头
                    row.CreateCell(0).SetCellValue("行号");
                    row.CreateCell(1).SetCellValue("单元格值");
                    row.CreateCell(2).SetCellValue("错误信息");
                    //创建内容
                    for (int i = 0; i < list.Count; i++) {
                        IRow row2 = errorSheet.CreateRow(i + 1);
                        row2.CreateCell(0).SetCellValue(list[i].RowNum + 1);
                        row2.CreateCell(1).SetCellValue(list[i].Value);
                        row2.CreateCell(2).SetCellValue(list[i].Message);
                    }
                } catch (Exception e) {
                    Logger.Error("创建错误页错误 WriteErrorMessage", e);
                } finally {
                    var anyfile = new FileStream(importFileName, FileMode.Create);
                    bookExit.Write(anyfile);
                    file.Close();
                    file.Dispose();
                    anyfile.Close();
                    anyfile.Dispose();
                }

                #endregion
            } else {
                #region 2007格式

                if (ext.Equals(".xlsx")) {
                    var fileInfo = new FileInfo(importFileName);
                    using (var package = new ExcelPackage(fileInfo)) {
                        try {
                            foreach (ExcelWorksheet item in package.Workbook.Worksheets) {
                                if (item.Name == "错误信息") {
                                    package.Workbook.Worksheets.Delete(item);
                                    break;
                                }
                            }

                            ExcelWorksheet sheet = package.Workbook.Worksheets.Add("错误信息");

                            sheet.Cells[1, 1].Value = "行号";
                            sheet.Cells[1, 2].Value = "单元格值";
                            sheet.Cells[1, 2].Value = "错误信息";

                            int k = 2;
                            foreach (ImportErrorCellInfo errorCell in list) {
                                sheet.Cells[k, 1].Value = errorCell.RowNum;
                                sheet.Cells[k, 2].Value = errorCell.Value;
                                sheet.Cells[k, 3].Value = errorCell.Message;
                                k++;
                            }
                        } catch {
                        } finally {
                            package.Save();
                        }
                    }
                }

                #endregion
            }
        }
    }
    public sealed class ImportErrorCellInfo
    {
        public bool IsError = false;
        public int RowNum;
        public string Message = string.Empty;
        public string Value = null;
        public ImportErrorCellInfo(int rowNum, string message)
            : this(rowNum, "", message) {

        }
        public ImportErrorCellInfo(int rowNum, string value, string message) {
            RowNum = rowNum;
            Value = value;
            Message = message;
            IsError = true;
        }


    }
}